3) Indexing in SQL - From the Absolute Basics to the Advanced Level

Previous Part: Normalization and Denormalization 

Let’s go deep into Indexing in SQL, from absolute beginner to advanced tuning, so you’ll understand not just what it is, but how to use it effectively (and when not to).

We’ll cover:

1. What indexing is (and why it matters)

2. Types of indexes (with syntax & examples)

3. How indexes work internally

4. Indexing strategies (practical scenarios)

5. Performance tuning & pitfalls

---

1. What is Indexing?

An index in a database is like an index in a book. It helps you find rows faster without scanning the entire table.

Without index:

• DB starts at the first row and checks every row until it finds the match (Full Table Scan).

With index:

• DB uses a pre-built data structure (like a sorted list or tree) to quickly jump to the matching rows.

---

Analogy:
Finding “Chapter 8” in a book:

• No index: You flip through every page

• With index: You check the book’s index and go directly to page 120

---

2. Types of Indexes in SQL

---

2.1 Single-Column Index

Use: Speeds up lookups on one column.

-- Create an index on the 'last_name' column
CREATE INDEX idx_lastname ON Employees(last_name);

Query benefit:

SELECT * FROM Employees WHERE last_name = 'Smith';

The DB will use the index to jump directly to all “Smith” rows.

---

2.2 Composite (Multi-Column) Index

Use: For queries filtering on multiple columns.

CREATE INDEX idx_lastname_firstname ON Employees(last_name, first_name);

Important:
Order matters — this index works for:

• WHERE last_name = 'Smith'

• WHERE last_name = 'Smith' AND first_name = 'John'

But not for:

• WHERE first_name = 'John' (skips the first column of the index)

---

2.3 Unique Index

Use: Ensures no duplicate values.

CREATE UNIQUE INDEX idx_email_unique ON Users(email);

This is often automatically created for PRIMARY KEY and UNIQUE constraints.

---

2.4 Full-Text Index

Use: For searching words inside large text columns.

CREATE FULLTEXT INDEX idx_content_text ON Articles(content);

Allows:

SELECT * FROM Articles WHERE MATCH(content) AGAINST('database indexing');

---

2.5 Covering Index

Use: Index that contains all columns needed for the query, avoiding table lookups.

CREATE INDEX idx_orders_customer_date ON Orders(customer_id, order_date);

If you query:

SELECT customer_id, order_date FROM Orders WHERE customer_id = 10;

The DB fetches all data directly from the index.

---

2.6 Clustered vs Non-Clustered Index

• Clustered Index → Sorts the actual table data by the index key (1 per table).

• Non-Clustered Index → Stores a separate structure that points to table rows (many allowed).

Example (SQL Server / MySQL InnoDB automatically clusters by PK):

CREATE CLUSTERED INDEX idx_empid ON Employees(emp_id);

---

3. How Indexes Work Internally

Most relational databases use a B-Tree (Balanced Tree) for indexes:

• Keeps data sorted

• Each search step reduces the search space drastically

• Example: Searching 1M rows → log₂(1,000,000) ≈ 20 steps

Some indexes use Hash Tables (fast for exact matches, bad for ranges).
Full-text indexes use Inverted Indexes (maps words → document IDs).

---

4. Indexing Strategies

---

Scenario 1: Frequently searching by email

CREATE INDEX idx_email ON Users(email);

---

Scenario 2: Filtering + Sorting

CREATE INDEX idx_status_date ON Orders(status, order_date);

Helps with:

SELECT * FROM Orders WHERE status = 'Shipped' ORDER BY order_date;

---

Scenario 3: Avoiding Over-Indexing

• Too many indexes → Slower INSERT, UPDATE, DELETE

• Indexes need to be maintained for every write

---

Scenario 4: Partial Indexes (PostgreSQL)

CREATE INDEX idx_active_users ON Users(last_login) WHERE is_active = true;

Only indexes active users → smaller & faster index.

---

5. Performance Tuning & Pitfalls

✅ Do:

• Index columns used in WHERE, JOIN, and ORDER BY

• Use covering indexes for read-heavy queries

• Periodically check unused indexes

❌ Avoid:

• Indexing every column (hurts writes & storage)

• Using indexes for small tables (full scan might be faster)

• Forgetting column order in composite indexes

---

Pro tip:
Check query execution plan with:

EXPLAIN SELECT ...;

You’ll see if an index is being used.

---

6. Summary Table

---

Perfect — let’s make a Beginner → Advanced SQL Indexing Practice Set that checks:

• Concept knowledge

• SQL syntax ability

• Practical performance judgment

I’ll give question → answer → explanation for each.

---

📚 SQL Indexing Practice Questions

---

🟢 Easy Level (Concept Basics)

1. Which statement about indexes is correct?
a) Indexes slow down SELECT queries
b) Indexes can speed up SELECT queries but may slow down INSERTs
c) Indexes always make every query faster
d) Indexes are the same as constraints

Answer: b) Indexes can speed up SELECT queries but may slow down INSERTs
Explanation:
Indexes help read performance, but every INSERT/UPDATE/DELETE also needs to update indexes, which can slow down writes.

---

2. True/False:
A unique index can be created without defining a PRIMARY KEY.

Answer: True
Explanation:
PRIMARY KEY automatically creates a unique index, but you can create standalone unique indexes for other columns.

---

3. What type of index is most useful for searching inside large text fields?

Answer: Full-Text Index
Explanation:
Full-text indexes allow word-based searches rather than exact matches.

---

---

🟡 Medium Level (SQL Usage)

4. Create an index on the email column of the Users table to speed up lookups.

Answer:

CREATE INDEX idx_email ON Users(email);

Explanation:
This creates a non-unique single-column index, useful for WHERE email = ... queries.

---

5. You often run:

SELECT * FROM Orders WHERE status = 'Shipped' ORDER BY order_date;

Create an index to optimize this.

Answer:

CREATE INDEX idx_status_date ON Orders(status, order_date);

Explanation:
A composite index improves filtering by status and sorting by order_date in one go.

---

6. Which of these queries will use the composite index:

CREATE INDEX idx_last_first ON Employees(last_name, first_name);

a) WHERE last_name = 'Smith'
b) WHERE first_name = 'John'
c) WHERE last_name = 'Smith' AND first_name = 'John'

Answer: a and c
Explanation:
Composite indexes work left to right — the first column in the index (last_name) must be used.

---

---

🔴 Hard Level (Performance & Strategy)

7. You have a large Logs table. Only recent entries (last 7 days) are queried frequently. How do you index efficiently?

Answer:
Create a partial index (PostgreSQL) or filtered index:

CREATE INDEX idx_recent_logs ON Logs(log_date) WHERE log_date >= CURRENT_DATE - INTERVAL '7 days';

Explanation:
This makes the index smaller and faster because it ignores old logs that aren’t queried.

---

8. Why might adding an index make a query slower?

Answer:

• Table is very small → full scan faster than index lookup

• Query returns most of the table → index lookup adds unnecessary overhead

• Too many indexes slow down writes due to maintenance cost

---

9. Your query:

SELECT customer_id, order_date FROM Orders WHERE customer_id = 42;

runs millions of times per day. How can you make it faster without reading the table?

Answer:
Create a covering index:

CREATE INDEX idx_customer_date ON Orders(customer_id, order_date);

Explanation:
This stores both customer_id and order_date in the index — so the DB doesn’t have to fetch the table rows at all.

---

10. You have a composite index (city, age) but your query is:

SELECT * FROM People WHERE age > 30;

Why is the index not used?

Answer:
Because the index order starts with city. Without filtering by city, the index can't efficiently seek by age.

---

---

📊 Summary Table - When to Use Which Index

---

Next Part: Query Optimization

Summary